resources
import datetime
from IPython.display import Markdown as md
import ccxt
import yfinance as yf
import numpy as np
import pandas as pd
import requests
import re
import plotly
import plotly.express as px
import statsmodels.api as sm
import scipy.optimize as spop
now = datetime.datetime.now()
md("Last Updated at {}".format(now.strftime("%Y/%m/%d %H:%M")))
Last Updated at 2022/08/19 01:23
exchange = ccxt.kraken ({"enableRateLimit": True})
candles = exchange.fetch_ohlcv("BTC/USD", "1d")
df_btc = pd.DataFrame(candles)
df_btc = df_btc.iloc[:, [0, 4]]
df_btc.columns = ["time", "btc_usd_close"]
df_btc["time"] = pd.to_datetime(df_btc["time"], unit="ms")
df_btc["str_time"] = pd.to_datetime(df_btc["time"], unit="ms").dt.strftime("%Y/%m/%d")
fig = px.line(df_btc, x="time", y="btc_usd_close")
fig.show()
# match start and end date with btc/usd
start_date = list(map(int, df_btc["str_time"].iloc[0].split('/')))
end_date = list(map(int, df_btc["str_time"].iloc[-1].split('/')))
start = datetime.datetime(start_date[0], start_date[1], start_date[2])
end = datetime.datetime(end_date[0], end_date[1], end_date[2])
print(start, end)
# download the stock price
stock = "MSTR"
df_mstr = yf.download(stock, start=start, end=end, progress=False).reset_index()
2020-08-30 00:00:00 2022-08-19 00:00:00
df_mstr = df_mstr[["Date", "Close"]].rename(columns = {"Date": "time", "Close": "mstr_close"})
df_mstr["str_time"] = df_mstr["time"].dt.strftime("%Y/%m/%d")
fig = px.line(df_mstr, x="time", y="mstr_close")
fig.show()
# 400 / month budget
# 35-40% from ATH: 175/week
# 40-45% from ATH: 260/week
# 45-50% from ATH: 425/week
# >50% from ATH: /week
def drop_from_ath():
# function to return
url = "https://www.coingecko.com/en/coins/bitcoin"
headers = {'User-agent': 'Mozilla/5.0'}
try:
response = requests.get(url=url, headers=headers)
response.encoding = response.apparent_encoding
except requests.exceptions.RequestException as e:
raise SystemExit(e)
tmp = pd.read_html(response.text)[0]
ath_percentage_str = tmp[tmp[0].str.contains("All-Time High")][1].values[0].split()[1]
ath_percentage_num = float(re.findall(r"[-+]?\d*\.\d+|\d+", ath_percentage_str)[0])
return ath_percentage_num
df = pd.merge(df_btc, df_mstr, on="str_time", how="inner")
df_price = df[["time_x", "btc_usd_close", "mstr_close"]]
df_price = df_price.rename(columns={"time_x": "date", "btc_usd_close": "BTC", "mstr_close":"MSTR"})
df_returns = pd.DataFrame()
for item in ["BTC", "MSTR"]:
df_returns[item] = np.append(df_price[item][1:].reset_index(drop=True)/df_price[item][:-1].reset_index(drop=True) -1, 0)
df_price = df_price.loc[(df_price["date"] > "2021-01-01"), :]
df_price["MSTR_BTC_ratio"] = np.log(df_price["MSTR"]) - np.log(df_price["BTC"])
fig = px.line(df_price, x="date", y="MSTR_BTC_ratio")
fig.show()
df_price["ratio_zscore"] = (df_price["MSTR_BTC_ratio"] - np.mean(df_price["MSTR_BTC_ratio"])) / np.std(df_price["MSTR_BTC_ratio"])
fig = px.line(df_price, x="date", y="ratio_zscore")
fig.add_hline(y=np.mean(df_price["ratio_zscore"]))
fig.show()
# initializing arrays
gross_returns = np.array([])
net_returns = np.array([])
t_stats = np.array([])
fair_values = np.array([])
diff_values = np.array([])
a_opts = np.array([])
b_opts = np.array([])
# rolling window
window = 256
t_threshold = 0.05
fee = 0.001 # 0.1%
for t in range(window, len(df_price)):
# define unit root function
# MSTR = a + b * BTC
def unit_root(b):
a = np.average(df_price["MSTR"][t-window:t] - b * df_price["BTC"][t-window:t])
fair_value = a + b * df_price["BTC"][t-window:t]
diff = np.array(fair_value - df_price["MSTR"][t-window:t])
# difference in difference
diff_diff = diff[1:] - diff[:-1]
# regress difference in difference by lagged difference
lin_reg = sm.OLS(diff_diff, diff[:-1])
result = lin_reg.fit()
# return Dicky-Fuller t-stat
return result.params[0]/result.bse[0]
# optimize cointegration parameters
result1 = spop.minimize(unit_root, df_price["MSTR"][t] / df_price["BTC"][t], method="Nelder-Mead")
t_opt = result1.fun
b_opt = float(result1.x)
a_opt = np.average(df_price["MSTR"][t-window:t] - b_opt * df_price["BTC"][t-window:t])
fair_value = a_opt + b_opt * df_price["BTC"][t]
diff_value = fair_value - df_price["MSTR"][t]
# simulate trading
if t == window:
old_signal = 0
if t_opt > t_threshold:
signal = 0
gross_return = 0
else:
signal = np.sign(fair_value - df_price["BTC"][t])
gross_return = signal * df_returns["MSTR"][t] - signal * df_returns["BTC"][t]
fees = fee * abs(signal - old_signal)
net_return = gross_return - fees
gross_returns = np.append(gross_returns, gross_return)
net_returns = np.append(net_returns, net_return)
t_stats = np.append(t_stats, t_opt)
fair_values = np.append(fair_values, fair_value)
diff_values = np.append(diff_values, diff_value)
a_opts = np.append(a_opts, a_opt)
b_opts = np.append(b_opts, b_opt)
# simple logging
#print('day ' + str(df_price.index[t]))
#print('----------')
#if signal == 0:
# print('No trading')
#elif signal == 1:
# print('long BTC, short MSTR.')
#else:
# print('short BTC, long MSTR.')
#print(f'gross daily return: {round(gross_return*100, 2)}%')
#print(f'net daily return: {round(net_return*100, 2)}%')
#print(f'cumulative net return: {round(np.prod(1+net_returns)*100 -100, 2)}%')
#print('----------')
old_signal = signal
df_result = pd.DataFrame({"gross_returns":np.append(1, np.cumprod(1+gross_returns)),
"net_returns": np.append(1, np.cumprod(1+net_returns))})
df_result["date"] = df_price["date"][window:].reset_index(drop=True)
fig = px.line(df_result, "date", "net_returns")
fig.show()
df_mstr = df_price.iloc[window:, ].copy()
df_mstr.loc[:, "fair_value"] = fair_values
df_mstr.loc[:, "diff_value"] = diff_values
df_mstr.loc[:, "t_stat"] = t_stats
df_mstr.loc[:, "a_opt"] = a_opts
df_mstr.loc[:, "b_opt"] = b_opts
#df_mstr.loc[abs(df_mstr["fair_value"])>10**8, "fair_value"] = 0
fig = px.line(df_mstr, "date", df_mstr["b_opt"])
fig.show()
# Last update 2022/06/11
# https://www.coingecko.com/en/public-companies-bitcoin
BTC_owned = 129218
last_btc_price = df_mstr.iloc[-1]["BTC"]
# https://stockanalysis.com/stocks/mstr/statistics/
num_shares = 11.30 * 10**6
last_mstr_price = df_mstr.iloc[-1]["MSTR"]
# MSTR intrinsic value
mstr_val = 1200000000
print(f"BTC price: {last_btc_price}")
print(f"MSTR close price: {last_mstr_price}")
print(f"BTC per share: {BTC_owned / num_shares}")
print(f"Spot BTC price: {last_btc_price * BTC_owned / num_shares}")
print(f"Discount/Premium: {round(((last_mstr_price * num_shares) / (last_btc_price * BTC_owned) -1) * 100, 2)}%")
BTC price: 23192.3 MSTR close price: 324.3800048828125 BTC per share: 0.011435221238938052 Spot BTC price: 265.209081539823 Discount/Premium: 22.31%